DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS items CASCADE;
DROP TABLE IF EXISTS comments CASCADE;
DROP TABLE IF EXISTS bookings CASCADE;
DROP TABLE IF EXISTS item_requests CASCADE;

CREATE TABLE IF NOT EXISTS users
(
    id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email varchar(320),
    name  varchar(100),
    UNIQUE (email)
);

CREATE TABLE IF NOT EXISTS items
(
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id         BIGINT,
    name            VARCHAR(100),
    description     VARCHAR(1000),
    available       BOOLEAN,
    item_Request_id BIGINT NULL,
    CONSTRAINT fk_items_to_users FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS item_requests
(
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    item_id     BIGINT,
    user_id     BIGINT,
    description VARCHAR(1000),
    date_create TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT fk_requests_to_items FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_requests_to_users FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS bookings
(
    id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    start_booking  TIMESTAMP WITHOUT TIME ZONE,
    end_booking    TIMESTAMP WITHOUT TIME ZONE,
    user_id        BIGINT,
    item_id        BIGINT,
    status_booking VARCHAR(100),
    CONSTRAINT fk_bookings_to_items FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_bookings_to_users FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS comments
(
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    text      VARCHAR(1000),
    item_id   BIGINT,
    author_id BIGINT,
    created   TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT fk_comments_to_items FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_comments_to_users FOREIGN KEY (author_id) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS users
(
    id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email varchar(320),
    name  varchar(100),
    UNIQUE (email)
);

CREATE TABLE IF NOT EXISTS items
(
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id         BIGINT,
    name            VARCHAR(100),
    description     VARCHAR(1000),
    available       BOOLEAN,
    item_Request_id BIGINT NULL,
    CONSTRAINT fk_items_to_users FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS item_requests
(
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    item_id     BIGINT,
    user_id     BIGINT,
    description VARCHAR(1000),
    date_create TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT fk_requests_to_items FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_requests_to_users FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS bookings
(
    id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    start_booking  TIMESTAMP WITHOUT TIME ZONE,
    end_booking    TIMESTAMP WITHOUT TIME ZONE,
    user_id        BIGINT,
    item_id        BIGINT,
    status_booking VARCHAR(100),
    CONSTRAINT fk_bookings_to_items FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_bookings_to_users FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS comments
(
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    text      VARCHAR(1000),
    item_id   BIGINT,
    author_id BIGINT,
    created   TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT fk_comments_to_items FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_comments_to_users FOREIGN KEY (author_id) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS users
(
    id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email varchar(320),
    name  varchar(100),
    UNIQUE (email)
);

CREATE TABLE IF NOT EXISTS items
(
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id         BIGINT,
    name            VARCHAR(100),
    description     VARCHAR(1000),
    available       BOOLEAN,
    item_Request_id BIGINT NULL,
    CONSTRAINT fk_items_to_users FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS item_requests
(
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    item_id     BIGINT,
    user_id     BIGINT,
    description VARCHAR(1000),
    date_create TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT fk_requests_to_items FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_requests_to_users FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS bookings
(
    id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    start_booking  TIMESTAMP WITHOUT TIME ZONE,
    end_booking    TIMESTAMP WITHOUT TIME ZONE,
    user_id        BIGINT,
    item_id        BIGINT,
    status_booking VARCHAR(100),
    CONSTRAINT fk_bookings_to_items FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_bookings_to_users FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS comments
(
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    text      VARCHAR(1000),
    item_id   BIGINT,
    author_id BIGINT,
    created   TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT fk_comments_to_items FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_comments_to_users FOREIGN KEY (author_id) REFERENCES users (id)
)